import datetime
from db_conn import pd_conn_zfw, executeSql


def queryPayTypes():
    sql = 'SELECT DISTINCT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0'
    field, result = executeSql(sql)

    payTypes = ''
    for p in result:

        if not payTypes:
            payTypes += p[0]
        else:
            payTypes += ',' + p[0]

    return payTypes


def query_new_user_country(startTime, endTime, table_name, payTypes):
    sql = f"""
        select o.f_user_id from {table_name} o
        where 
            o.f_order_date >= '{startTime}' and o.f_order_date < '{endTime}' 
            and (o.f_status in (50, 60) OR ( o.f_status = 15 AND o.f_pay_type IN ({payTypes}) ) )
            AND f_is_insider = 0 AND f_service_id = 2002
        group by o.f_user_id
    """
    return pd_conn_zfw(sql)


def query_new_user_city(startTime, endTime, table_name, payTypes):
    sql = f"""
        select o.f_user_id from {table_name} o
        left join t_factory f on f.f_id = o.f_factory_id
        where 
            o.f_order_date >= '{startTime}' and o.f_order_date < '{endTime}' 
            and (o.f_status in (50, 60) OR ( o.f_status = 15 AND o.f_pay_type IN (
                {payTypes}) ) )
            AND f_is_insider = 0 AND f_service_id = 2002 and f.f_city_id = 3301 -- 杭州
        group by o.f_user_id
    """
    return pd_conn_zfw(sql)


if __name__ == '__main__':

    # 本次统计开始日
    startTime = '2022-08-01'
    # 本次统计结束日后一天
    endTime = '2022-11-01'
    payTypes = queryPayTypes()

    # 2021年order表暂未分表，分表后需调整表名
    while startTime != endTime:
        table_name = 't_order' if int(startTime[:4]) >= 2021 else 't_order_' + startTime[:4]
        reduceOneDay = (datetime.datetime.strptime(startTime, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
            day=1).strftime("%Y-%m-%d")
        df1 = query_new_user_country(startTime, reduceOneDay, table_name, payTypes)
        df1.to_csv(r'D:\Store\Python-Download\全国月新用户\全国月用户_{}.csv'.format(startTime[:7]))
        df2 = query_new_user_city(startTime, reduceOneDay, table_name, payTypes)
        df2.to_csv(r'D:\Store\Python-Download\杭州月新用户\杭州月用户_{}.csv'.format(startTime[:7]))
        startTime = reduceOneDay
        print(reduceOneDay)
